import datetime
import pymysql
from iotdb.dbapi import connect


class OPEN_IOTDB:
    def __init__(self):
        self.iotdb_ip = "127.0.0.1"
        self.iotdb_port = "6667"
        self.iotdb_username = "root"
        self.iotdb_password = "root"

    def iotdb(self):
        self.conn = connect(
            self.iotdb_ip,
            self.iotdb_port,
            self.iotdb_username,
            self.iotdb_password,
            fetch_size=1024,
            zone_id="UTC+8",
            sqlalchemy_mode=False
        )

    def activity_tend(self,item_name):
        self.iotdb()
        self.cursor = self.conn.cursor()

        data = []

        sql = f"select activity from root.opendigger.{item_name} ORDER BY TIME DESC LIMIT 10"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        for item in result:
            timestamp = item[0] / 1000 + 86400  # 时间戳除以1000，转换为秒
            date = datetime.datetime.utcfromtimestamp(timestamp).strftime('%Y-%m-%d')  # 转换为年月日
            item[0] = date  # 更新原数据中的时间戳部分
        result.insert(0, item_name)
        # 获取预测数据
        ope=OPEN_MYSQL()
        predict = ope.data_predict(item_name)
        result.append(predict)
        data.append(result)

        self.cursor.close()
        self.conn.close()
        return data


class OPEN_MYSQL:
    def __init__(self):
        self.mysql_host = "127.0.0.1"
        self.mysql_port = 3306
        self.mysql_username = "root"
        self.mysql_password = "123456"
        self.database="opendigger"

    def Connect(self):
        self.con = pymysql.connect(
            host=self.mysql_host,
            port = self.mysql_port,
            user = self.mysql_username,
            password = self.mysql_password,
            database = self.database,
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor      # 查询结果返回为字典格式，其中字段名作为字典的键
        )

    def act_proportion(self,item_name):
        self.Connect()
        self.cursor = self.con.cursor()
        try:
            sql=f"select * from act_proportion WHERE item='{item_name}'"
            # print(sql)
            # 执行sql命令
            self.cursor.execute(sql)
            # 获取所有查询结果
            results = self.cursor.fetchall()
            # results.insert(0, 'airflow')
            return results

        except Exception as e:
            print(e)
        finally:
            self.cursor.close()
            self.con.close()

    def data_predict(self, item_name):
        self.Connect()
        self.cursor = self.con.cursor()
        try:
            sql = f"select future_data from lstm_predicts WHERE item='{item_name}'"
            # print(sql)
            # 执行sql命令
            self.cursor.execute(sql)
            # 获取所有查询结果
            results = self.cursor.fetchall()
            # results.insert(0, 'airflow')
            return results

        except Exception as e:
            print(e)
        finally:
            self.cursor.close()
            self.con.close()

def inquire(item_name):
    select=OPEN_MYSQL()
    model = OPEN_IOTDB()

    acts_proportion=select.act_proportion(item_name)

    activity_tende=model.activity_tend(item_name)
    return acts_proportion,activity_tende
    # return activity_tende


# data=inquire('angular')
# print(data)

